package io.codegeneratetool.web.service;

import io.codegeneratetool.util.StringUtils;
import io.codegeneratetool.web.common.PageRequest;
import io.codegeneratetool.web.common.PagerResult;
import io.codegeneratetool.web.entity.DBConnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

/** 数据库连接设置的增删改查
 * @author jifsu
 *
 */
@Service("dBConnectionService")
public class DBConnectionService {

	@Resource(name="jdbcTemplate_codegen")
	JdbcTemplate jdbcTemplate;
	
	public int createOrUpdateEntity(DBConnection  connection){
		String  sql;
		if(StringUtils.isNullOrEmpty(connection.getDbid())){
			sql="insert into CODE_DBCONNECTION(dbid,createdtime,descr,DRIVERNAME,JDBCURL,USERNAME,PASSWD) values(?,?,?,?,?,?,?)";
			return jdbcTemplate.update(sql,StringUtils.getUUID()
					, new Date() 
					,connection.getDescr()
					,connection.getDrivername()
					,connection.getJdbcurl()
					,connection.getUsername()
					,connection.getPasswd()
					);
		}else{
			sql="update CODE_DBCONNECTION set descr=?,DRIVERNAME=?,JDBCURL=?,USERNAME=?,PASSWD=? where dbid=?";
			return jdbcTemplate.update(sql, 
					 connection.getDescr()
					,connection.getDrivername()
					,connection.getJdbcurl()
					,connection.getUsername()
					,connection.getPasswd()
					,connection.getDbid()
					);
		}
		 
	}
	
	
	public PagerResult findAll(PageRequest pageRequest){ 
		String countSql = "select count(*) from CODE_DBCONNECTION ";
		String sql = "select * from CODE_DBCONNECTION  order by createdtime limit ?,?";
		Long queryForObject = jdbcTemplate.queryForObject(countSql, Long.class);
		List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(sql,pageRequest.getFirst(),pageRequest.getPagesize());
		PagerResult result = new PagerResult (pageRequest.getPage(), queryForObject.intValue(), queryForList.size() , queryForList);
		return result;
		
	}
	
	public DBConnection findById(String id){ 
		String sql = "select * from CODE_DBCONNECTION where dbid=?";
		
		try {
			return jdbcTemplate.queryForObject(sql,new Object[]{id},new RowMapper<DBConnection>(){

				public DBConnection mapRow(ResultSet rs, int idx)
						throws SQLException {
					DBConnection connection = new DBConnection();
					connection.setDbid(rs.getString("DBID"));
					connection.setCreatedtime(new Timestamp( rs.getLong("CREATEDTIME") )); 
					connection.setDescr(rs.getString("DESCR"));
					connection.setDrivername(rs.getString("DRIVERNAME"));
					connection.setUsername(rs.getString("USERNAME"));
					connection.setPasswd(rs.getString("PASSWD"));
					connection.setJdbcurl(rs.getString("JDBCURL"));
					
					return connection;
				}
				
			});
		} catch ( Exception e) {
			return null;
		} 
	}
	public int del(String id){ 
		String sql = "delete from CODE_DBCONNECTION where dbid=?";
		return jdbcTemplate.update(sql,  id ); 
	}
}
